package com.wuseguang.springcloud.dao.jdbc;

import com.wuseguang.springcloud.JdbcTemplate.BaseTemplate;
import com.wuseguang.springcloud.entity.Classes;
import com.wuseguang.springcloud.entity.ClassesList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class OrganizationalStructureTemplate extends BaseTemplate {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    public List<Map<String, Object>> getMap1(String sql, String key, String key1) {
        return dao.query(sql, rs -> {
            List<Map<String, Object>> maps = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put(key, rs.getString(key));
                map.put(key1, rs.getString(key1));
                maps.add(map);
            }
            return maps;
        });
    }


    public List<Map<String, Object>> queryDepartment(String sql, String key, String key1, String schoolId) {
        String sq3 = "SELECT id count FROM department WHERE school_id=? order by id LIMIT 1";
        Integer integer = queryCount(sq3, schoolId);
        return dao.query(sql, rs -> {
            List<Map<String, Object>> maps = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                if ((integer + "").equals(rs.getString(key))) {
                    List<Map<String, Object>> stringObjectMap = queryGrade(schoolId);
                    map.put("gradelist", stringObjectMap);
                }
                map.put(key, rs.getString(key));
                map.put(key1, rs.getString(key1));
                maps.add(map);
            }
            return maps;
        });
    }


    public List<Map<String, Object>> queryGrade(String schoolId) {
        String sql4 = "SELECT t.id education_id,t.nameofdepartment,IF((SELECT COUNT(*) FROM grade g WHERE other!=1 AND g.education_id=t.id  AND g.school_id=" + schoolId + " )>0,1,0) state FROM school_template t WHERE (school_id=0 OR school_id=" + schoolId + ")";
        //学部
        List<Map<String, Object>> maps2 = getMap2(sql4, "education_id", "nameofdepartment", "state");
        if (maps2 != null && maps2.size() > 0) {
            for (Map<String, Object> stringObjectMap : maps2) {
                //年级
                String sql1 = "SELECT  g.id gradeid,g.name gradename  FROM grade g WHERE g.education_id=" + stringObjectMap.get("education_id") + " and g.school_id=" + schoolId + " ORDER BY other";
                List<Map<String, Object>> map11 = getMap1(sql1, "gradeid", "gradename");
                if (map11 != null && map11.size() > 0) {
                    for (Map<String, Object> stringObjectMap1 : map11) {
                        String sql2 = "SELECT s.id classid , s.name classname FROM  classes s WHERE s.graduation=0  and s.grade_id=" + stringObjectMap1.get("gradeid") + " and s.school_id=" + schoolId;
                        //班级
                        List<Map<String, Object>> map12 = getMap1(sql2, "classid", "classname");
                        stringObjectMap1.put("classlist", map12);
                    }
                    stringObjectMap.put("gradelist", map11);
                }
            }
        }
        return maps2;
    }


    public List<Map<String, Object>> getMap2(String sql, String key, String key1, String key2) {
        return dao.query(sql, rs -> {
            List<Map<String, Object>> maps = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put(key, rs.getString(key));
                map.put(key1, rs.getString(key1));
                map.put(key2, rs.getString(key2));
                maps.add(map);
            }
            return maps;
        });
    }

    @Transactional
    public int[] batchUpdate(String listSql, List<Long> ids, String schoolId, List<String> a) {
        int[] ints = null;
        for (int j = 0; j < ids.size(); j++) {
            Long aLong = ids.get(j);
            String s = a.get(j);
            ints = dao.batchUpdate(listSql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, s + createClass(i + 1));//班级名称
                    ps.setString(2, schoolId);//学校id
                    ps.setLong(3, aLong);//年级id
                }

                @Override
                public int getBatchSize() {
                    return 6;//创建班级的数量
                }
            });
        }
        return ints;
    }


    public int batchUpDateClass(String listSql, ClassesList classesList) {
        try {
            List<Classes> classesList1 = classesList.getClassesList();
            return dao.batchUpdate(listSql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Classes classes = classesList1.get(i);
                    logger.info("classes.getSecondName()={},classes.getId()", classes.getSecondName(), classes.getId());
                    ps.setString(1, classes.getSecondName());
                    ps.setInt(2, classes.getId());
                }

                @Override
                public int getBatchSize() {
                    return classesList1.size();
                }
            }).length;
        } catch (Exception e) {
            e.printStackTrace();
            logger.info("批量添加异常,error={}", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();// 手动回滚
        }
        return 0;
    }

    @Transactional
    public int[] batchUpdateDepartMent(String listSql, String schoolId) {
        return dao.batchUpdate(listSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, "教务部门");
                ps.setString(2, schoolId);
            }

            @Override
            public int getBatchSize() {
                return 10;
            }
        });
    }

    public String createClass(int i) {
        String name = "";
        if (i == 1) {
            name = "(1)班";
        }
        if (i == 2) {
            name = "(2)班";
        }
        if (i == 3) {
            name = "(3)班";
        }
        if (i == 4) {
            name = "(4)班";
        }
        if (i == 5) {
            name = "(5)班";
        }
        if (i == 6) {
            name = "(6)班";
        }
        if (i == 7) {
            name = "(7)班";
        }
        if (i == 8) {
            name = "(8)班";
        }
        if (i == 9) {
            name = "(9)班";
        }
        if (i == 10) {
            name = "(10)班";
        }
        return name;
    }
}
